package TimesheetManagemen;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class EmployeeDAO {
	private Connection conn=null;
	public EmployeeDAO() {
		String userName = "root";
		String password = "123456";
		String url = "jdbc:mysql://localhost/timesheet?"
				+ "user=root&password=123456";

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			conn = java.sql.DriverManager
					.getConnection(url, userName, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}}
	public int addEmployee(Employee emp){
		
		String query="insert into employee values(?,?,?,?)";
		int kq=0;
		try {
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setInt(1, emp.getId());
			ps.setString(2, emp.getName());
			ps.setString(3, emp.getUsername());
			ps.setString(4, emp.getEmail());
			kq= ps.executeUpdate();
			return kq;
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return kq;
	}
	public void addEmp(Employee emp) throws ClassNotFoundException, SQLException{
		EmployeeDAO dao = new EmployeeDAO();
		ArrayList<Employee> list = dao.getEmployee();
	    int a=0;
		for(int i=0;i<list.size();i++)
		{
			int c=list.get(i).getId();
			if(c==emp.getId())
			{a=1;
			break;}
		}
		 if( a==1){
				System.out.println("Id exist");	
		    }   
		     else if(dao.addEmployee(emp)==1) {
		    	 
		    	 
		     	System.out.println("Add Successfull");
		}
		     else if(dao.addEmployee(emp)==0 ){
					System.out.println("Not Add Successfull");
			     }}
	public int updateEmployee(Employee emp) {
		String query = "update employee set name=?, username=? , email=? where id=?";
		int kq = 0;
		try {
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setString(1, emp.getName());
			ps.setString(2, emp.getUsername());
			ps.setString(3, emp.getEmail());
			ps.setInt(4, emp.getId());
			kq = ps.executeUpdate();
			return kq;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return kq;
	}
	public void updateEmp(Employee emp) throws ClassNotFoundException, SQLException{
		EmployeeDAO dao=new EmployeeDAO();
		ArrayList<Employee> list=dao.getEmployee();
		int a=0;
		for(int i=0;i<list.size();i++){
			int c=list.get(i).getId();
		if(c==emp.getId()){
			a=1;break;
		}}
		if(a==1){	 
			    
		int kq = dao.updateEmployee(emp);
	    if(kq!=0){
	    	System.out.print("Update Successful");
		}else{
			System.out.println("Not Update Successfull");
		}}
		else{
			System.out.println("Not Found Id ");
	    }}
	public int deleteEmployee(int id) {
		String query = "delete from employee where id=?";
		int kq = 0;
		
		try {
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setInt(1, id);
			kq = ps.executeUpdate();
			return kq;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return kq;
	}

	public ArrayList<Employee> getEmployee() throws ClassNotFoundException, SQLException {
		ArrayList<Employee> dsEmp = new ArrayList<>();

		String query = "SELECT * FROM employee";
		try {
			PreparedStatement ps = conn.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			//System.out.println("id\t\tname\t\t\tusername\t\temail");
			while (rs.next()) {
				int id = rs.getInt(1);
				String username = rs.getString(2);
				String password = rs.getString(3);
				String email = rs.getString(4);
				Employee em = new Employee(id, username, password, email);
				dsEmp.add(em);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dsEmp;
	}

	/*
	 * public int insertEmployee(Connection connection, Employee emp) throws
	 * ClassNotFoundException, SQLException { String query =
	 * "INSERT INTO employee VALUES ("
	 * +emp.getId()+",'"+emp.getUsername()+"','"+emp
	 * .getPassword()+"',"+emp.getEmail()+")"; java.sql.Statement st =
	 * connection.createStatement(); int rs = st.executeUpdate(query); return
	 * rs; } public ResultSet viewEmployee(Connection conn) throws
	 * ClassNotFoundException, SQLException{ String query =
	 * "SELECT * FROM employee"; Statement st =(Statement)
	 * conn.createStatement(); ResultSet rs = st.executeQuery(query); return rs;
	 * 
	 * } public int updateEmployee(Connection conn, Employee emp)throws
	 * ClassNotFoundException, SQLException{ String query =
	 * "UPDATE employee SET username ='"
	 * +emp.getUsername()+"', password = "+emp.getPassword
	 * ()+", email = '"+emp.getEmail()+"' WHERE id = "+emp.getId()+""; Statement
	 * st = (Statement) conn.createStatement(); int rs =
	 * st.executeUpdate(query); return rs; } public int
	 * deleteEmployee(Connection conn, Employee emp)throws
	 * ClassNotFoundException, SQLException{ String query =
	 * "DELETE FROM employee WHERE id = '"+emp.getId()+"'"; Statement st =
	 * (Statement) conn.createStatement(); int rs = st.executeUpdate(query);
	 * return rs; }
	 */
}
